package JDBC;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import SwingModel.CmsAssignment;
import SwingModel.CmsGradebook;
import SwingModel.CmsQuiz;
import SwingModel.CmsStudent;

public class InstructorGradebookActivities {

	public static double getAsgmGrade(String username, int asgmID){
		double givenPoint = 0.0d;
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("select grade from dbcms.User_Assig where AssigId = ? and username = ?;");
			pstmt.setInt(1, asgmID);
			pstmt.setString(2,username);
			ResultSet resultSet = pstmt.executeQuery();
			while(resultSet.next()){
				givenPoint = resultSet.getDouble("grade");
			}
		}catch(Exception e){e.printStackTrace();}
		
		return givenPoint;
	}
	
	public static double getQuizGrade(String username, int quizID){
		double givenPoint = 0.0d;
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("select grade from dbcms.User_Tests where TestId = ? and username = ?;");
			pstmt.setInt(1, quizID);
			pstmt.setString(2,username);
			ResultSet resultSet = pstmt.executeQuery();
			while(resultSet.next()){
				givenPoint = resultSet.getDouble("grade");
			}
		}catch(Exception e){e.printStackTrace();}
		
		return givenPoint;
	}
	
	public static String getAsgmTitle(int asgmID) {
		String title = "";
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("select AssigTitle from dbcms.Assignments where AssigId = ?;");
			pstmt.setInt(1,asgmID);
			ResultSet resultSet = pstmt.executeQuery();
			
			while(resultSet.next()){
				title = resultSet.getString("AssigTitle");
			}
			
		}catch (Exception e){ e.printStackTrace();}
		
		return title;
	}
	
	public static String getQuizTitle(int quizID) {
		String title = "";
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("select TestTitle from dbcms.Tests where TestId = ?;");
			pstmt.setInt(1,quizID);
			ResultSet resultSet = pstmt.executeQuery();
			
			while(resultSet.next()){
				title = resultSet.getString("AssigTitle");
			}
			
		}catch (Exception e){ e.printStackTrace();}
		
		return title;
	}
	
	public static CmsGradebook getGradebook(String courseID){
		CmsGradebook gradebook = null;
		
		try{
			List<CmsStudent> students = PeopleActivities.getListOfStudent(courseID);
			List<CmsQuiz> quizzes = InstructorQuizActivities.getListOfQuiz(courseID);
			List<CmsAssignment> asgms = InstructorAssignmentActivities.getListOfAssignments(courseID);
			gradebook = new CmsGradebook(students, asgms, quizzes);
			
		}catch(Exception e){e.printStackTrace();}
		
		return gradebook;
	}
}
